//这是操作所有关于文章的数据模块

const conn = require('../utils/myconn');
// 获取所有文章的方法
exports.getAllPosts = (obj, callback) => {
   // 创建sql语句
   let sql = `SELECT posts.*,users.nickname,categories.\`name\`
   from posts
   JOIN users on posts.user_id = users.id
   JOIN categories on posts.category_id = categories.id
   where 1=1 `//加恒等于之后，后面不管判断结果如何，都不会影响

   if (obj.cate && obj.cate != 'all') {
      sql += ` and categories.id = '${obj.cate}' `
   }
   if (obj.status && obj.status != 'all') {
      sql += ` and posts.status = '${obj.status}' `
   }

   sql += ` ORDER BY id DESC
   limit ${(obj.pageNum - 1) * obj.pageSize},${obj.pageSize}`;
   // 执行sql语句
   conn.query(sql, (err, results) => {
      if (err) {
         callback(err);
      } else {
         //再获取总记录
         let sql = `select count(*) as cnt 
         from posts
         join users on posts.user_id = users.id
         join categories on posts.category_id = categories.id
         where 1=1  `
         if (obj.cate && obj.cate != 'all') {
            sql += ` and categories.id = '${obj.cate}' `
         }
         if (obj.status && obj.status != 'all') {
            sql += ` and posts.status = '${obj.status}' `
         }

         conn.query(sql, (err2, res2) => {
            if (err2) {
               callback(err2)
            } else {
               callback(null, { data: results, total: res2[0].cnt })
            }
         })
      }
   })
}


// 添加文章的方法
exports.addPost = (obj,callback)=>{
   // 创建sql语句
   let sql = `insert into posts set ?`
   // 执行sql语句
   conn.query(sql,obj,(err,results)=>{
      if(err){
         callback(err)
      }else{
         callback(null)
      }
   })
}


// 通过id获取文章的方法
exports.getPostById = (id,callback)=>{
   let sql = `SELECT * FROM posts WHERE id = '${id}'`;
   conn.query(sql,(err,results)=>{
      if(err){
         callback(err)
      }else{
         // 需要的是一个文章对象
         callback(null,results[0])
      }
   })
}

// 通过id编辑文章的方法
exports.editPostById = (obj,callback)=>{
   let sql =`update posts set ? where id= ?`;
   conn.query(sql,[obj,obj.id],(err,results)=>{
      if(err){
         callback(err)
      }else{
         callback(null)
      }
   })
}

//通过id删除文章的方法
exports.deletePostById = (id,callback)=>{
   let sql = 'DELETE from posts WHERE id = ?'
   conn.query(sql,id,(err,results)=>{
      if(err){
         callback(err)
      }else{
         callback(null,results)
      }
   })
}